﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Kaskelot.Services.Model;
using Stockholm.SOA.Kaskelot.WCF.Core;
using Stockholm.SOA.Kaskelot.WCF.ServiceImplementation.Mapping;

namespace Stockholm.SOA.Kaskelot.WCF.ServiceImplementation.Repositories
{
	internal class AnalysRepository : IDisposable
	{
		private KaskelotEntities context;
		public AnalysRepository()
		{
			this.context = new KaskelotEntities();
		}

		public void Dispose()
		{
			this.context.Dispose();
		}

		internal DataContracts.Analys.QueryResult ExecuteQuery(DataContracts.Analys.Query query, Guid valId)
		{
			string sqlQuery = string.Empty;
			string viewName = string.Empty;
			var checkedEntities = query.Entities.Where(e => e.IsChecked).ToList();

			DataContracts.Analys.Entity primaryEntity = checkedEntities.Single(e => e.IsPrimary);
			List<DataContracts.Analys.Entity> nonPrimaryEntity = checkedEntities.Where(e => !e.IsPrimary).ToList();

			//Remove space if present to primary entity name
			viewName = primaryEntity.Name.Replace(" ", string.Empty) + "Full";

			//get columnnames and update primary fields
			List<string> newColumnNames = primaryEntity.Fields.Where(fi => fi.IsChecked).Select(fi => fi.InternalName).ToList();
			List<DataContracts.Analys.Field> oldColumnFields = primaryEntity.Fields.Where(fi => fi.IsChecked).ToList();

			foreach (DataContracts.Analys.Entity e in nonPrimaryEntity)
			{
				newColumnNames.AddRange(e.Fields.Where(fi => fi.IsChecked).Select(fi => IsUsingJoinedView(primaryEntity) ? fi.InternalName : string.Format("{0}{1}", primaryEntity.Name, fi.InternalName)).ToList());
				oldColumnFields.AddRange(e.Fields.Where(fi => fi.IsChecked).ToList());
			}

			List<string> colNames = new List<string>();

			for (int i = 0; i < oldColumnFields.Count; i++)
			{
				colNames.Add(newColumnNames[i] + " as " + oldColumnFields[i].InternalName);
			}

			//sätt ihop joins
			sqlQuery = "SELECT DISTINCT " + string.Join(", ", colNames.ToArray()) + " FROM " + viewName;

			//Fixa villkor
			if (query.Filters.Count > 0)
			{
				query.Filters.ForEach(f => UpdateInteralFieldNameRecursive(f, GetEntities(), primaryEntity));

				sqlQuery += " WHERE ";

				sqlQuery += string.Join(" AND ", query.Filters.Select(f => BuildWhereClauseRecursive(f)));
			}

			if (sqlQuery.Contains("WHERE"))
				sqlQuery += " AND ValId = '" + valId.ToString("D") + "'";
			else
				sqlQuery += " WHERE ValId = '" + valId.ToString("D") + "'";

			Logger.LogVerbose("AnalyseRepository.ExecuteQuery: Running query " + sqlQuery);

			var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["KaskelotDb"].ConnectionString);
			connection.Open();
			SqlCommand comm = new SqlCommand(sqlQuery, connection);
			var reader = comm.ExecuteReader();

			List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();

			while (reader.Read())
			{
				Dictionary<string, object> row = new Dictionary<string, object>();

				for (int i = 0; i < oldColumnFields.Count; i++)
				{
					var field = oldColumnFields[i];
					if (reader.IsDBNull(i))
					{
						row.Add(field.InternalName, null);
						continue;
					}

					switch (field.DataTypeInGrid)
					{
						case "System.String":
							string val = reader.GetString(i);
							val = string.IsNullOrEmpty(val) ? val : val.RemoveHtmlTags();
							row.Add(field.InternalName, val);
							break;
						case "System.Nullable`1[System.DateTime]":
						case "System.DateTime":
							row.Add(field.InternalName, reader.GetDateTime(i));
							break;
						case "System.TimeSpan":
							row.Add(field.InternalName, reader.GetTimeSpan(i));
							break;
						case "System.Int32":
							row.Add(field.InternalName, reader.GetInt32(i));
							break;
						case "System.Decimal":
							row.Add(field.InternalName, reader.GetDecimal(i));
							break;
						case "System.Boolean":
							row.Add(field.InternalName, reader.GetBoolean(i));
							break;
						default:
							break;
					}
				}
				data.Add(row);
			}

			connection.Close();

			DataContracts.Analys.QueryResult result = new DataContracts.Analys.QueryResult();

			result.Data = data;
			result.ErrorMessage = string.Empty;
			result.HasError = false;

			return result;
		}

		// Todo
		private bool IsUsingJoinedView(DataContracts.Analys.Entity e)
		{
			return true;
			//bool retval = false;
			//switch (e.Name)
			//{
			//	case "Person":
			//		retval = true;
			//		break;
			//	case "Grupp":
			//		retval = true;
			//		break;
			//	case "Valdistrikt":
			//		retval = true;
			//		break;
			//	case "Forsamling":
			//		retval = true;
			//		break;
			//	case "Valkrets":
			//		retval = true;
			//		break;
			//}

			//return retval;
		}

		private void UpdateInteralFieldNameRecursive(DataContracts.Analys.IFilterPart part, List<DataContracts.Analys.Entity> enteties, DataContracts.Analys.Entity primaryEntity)
		{

			if (part is DataContracts.Analys.FieldFilter)
			{
				DataContracts.Analys.FieldFilter filter = part as DataContracts.Analys.FieldFilter;
				DataContracts.Analys.Entity e = GetEntityFromField(filter.Field, enteties);

				bool isPrimary = e.Name == primaryEntity.Name;

				if (isPrimary || IsUsingJoinedView(primaryEntity))
				{ }
				else
				{
					filter.Field.InternalName = string.Format("{0}{1}", primaryEntity.Name, filter.Field.InternalName);
					if (!string.IsNullOrEmpty(filter.Field.LookupColumn))
					{
						filter.Field.LookupColumn = string.Format("{0}{1}", primaryEntity.Name, filter.Field.LookupColumn);
					}
				}
			}
			else
			{
				DataContracts.Analys.FilterGroup group = part as DataContracts.Analys.FilterGroup;
				group.Parts.ForEach(p => UpdateInteralFieldNameRecursive(p, enteties, primaryEntity));
			}
		}

		private DataContracts.Analys.Entity GetEntityFromField(DataContracts.Analys.Field field, List<DataContracts.Analys.Entity> enteties)
		{
			foreach (DataContracts.Analys.Entity ent in enteties)
			{
				foreach (DataContracts.Analys.Field f in ent.Fields)
				{
					if (f.InternalName == field.InternalName)
					{
						return ent;
					}
				}
			}
			return null;
		}

		private string BuildWhereClauseRecursive(DataContracts.Analys.IFilterPart part)
		{
			string sql = string.Empty;
			if (part is DataContracts.Analys.FieldFilter)
			{
				DataContracts.Analys.FieldFilter filter = part as DataContracts.Analys.FieldFilter;
				sql += GetExpression(filter.Operator, filter.Field, filter.Value);
			}
			else
			{
				DataContracts.Analys.FilterGroup group = part as DataContracts.Analys.FilterGroup;
				sql += "(";
				string logicalop = group.Operator == DataContracts.Analys.GroupOperator.And ? "AND" : "OR";

				//Add the stuff in between
				IEnumerable<string> substrings = group.Parts.Select(p => BuildWhereClauseRecursive(p));
				sql += string.Join(" " + logicalop + " ", substrings.ToArray());

				sql += ")";
			}
			return sql;
		}

		private string GetExpression(DataContracts.Analys.FilterOperator op, DataContracts.Analys.Field field, object filterValue)
		{
			string sqlop = string.Empty;
			switch (op)
			{
				case DataContracts.Analys.FilterOperator.IsLessThan:
					sqlop = GetSimpleCondition("<", field.InternalName, filterValue);
					break;
				case DataContracts.Analys.FilterOperator.IsLessThanOrEqualTo:
					sqlop = GetSimpleCondition("<=", field.InternalName, filterValue);
					break;
				case DataContracts.Analys.FilterOperator.IsEqualTo:
					if (string.IsNullOrEmpty(field.LookupColumn))
						sqlop = GetSimpleCondition("=", field.InternalName, filterValue);
					else
						sqlop = GetSimpleCondition("=", field.LookupColumn, filterValue);
					break;
				case DataContracts.Analys.FilterOperator.IsNotEqualTo:
					if (string.IsNullOrEmpty(field.LookupColumn))
						sqlop = GetSimpleCondition("!=", field.InternalName, filterValue);
					else
						sqlop = GetSimpleCondition("!=", field.LookupColumn, filterValue);
					break;
				case DataContracts.Analys.FilterOperator.IsGreaterThanOrEqualTo:
					sqlop = GetSimpleCondition(">=", field.InternalName, filterValue);
					break;
				case DataContracts.Analys.FilterOperator.IsGreaterThan:
					sqlop = GetSimpleCondition(">", field.InternalName, filterValue);
					break;
				case DataContracts.Analys.FilterOperator.StartsWith:
					sqlop = field.InternalName + " like \'" + filterValue.ToString() + "%\'";
					break;
				case DataContracts.Analys.FilterOperator.EndsWith:
					sqlop = field.InternalName + " like \'%" + filterValue.ToString() + "\'";
					break;
				case DataContracts.Analys.FilterOperator.Contains:
					sqlop = field.InternalName + " like \'%" + filterValue.ToString() + "%\'";
					break;
				case DataContracts.Analys.FilterOperator.DoesNotContain:
					sqlop = field.InternalName + " not like \'%" + filterValue.ToString() + "%\'";
					break;

				//Not supported
				case DataContracts.Analys.FilterOperator.IsContainedIn:
				case DataContracts.Analys.FilterOperator.IsNotContainedIn:
					break;
				default:
					break;
			}
			return sqlop;
		}

		private string GetSimpleCondition(string op, string fieldName, object value)
		{
			string valueAsString = GetValueAsString(value);

			return fieldName + " " + op + " " + valueAsString;
		}

		private static string GetValueAsString(object value)
		{
			string valueAsString = string.Empty;
			switch (value.GetType().FullName)
			{
				case "System.DateTime":
				case "System.String":
				case "System.Guid":
					valueAsString = "\'" + value.ToString() + "\'";
					break;
				case "System.Float":
				case "System.Double":
				case "System.Decimal":
				case "System.Int32":
					valueAsString = value.ToString();
					break;
				case "System.Boolean":
					bool val = bool.Parse(value.ToString());
					valueAsString = val == true ? "1" : "0";
					break;
				default:
					break;
			}
			return valueAsString;
		}

		internal List<DataContracts.Analys.LookupField> GetLookupInfo()
		{
			List<DataContracts.Analys.LookupField> data = new List<DataContracts.Analys.LookupField>();

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "ByggnadID",
				Values = context.Byggnad.Select(w => new DataContracts.Analys.LookupValue { ID = w.Id, Value = w.Benamning }).ToList()
			});

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "PersonID",
				Values = context.Person.Select(t => new DataContracts.Analys.LookupValue { ID = t.Id, Value = t.Fornamn + " " + t.Efternamn }).OrderBy(v => v.Value).ToList()
			});

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "ByggnadTypID",
				Values = context.ByggnadTyp.Select(pt => new DataContracts.Analys.LookupValue { ID = pt.Id, Value = pt.Benamning }).OrderBy(v => v.Value).ToList()
			});

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "PersonTypID",
				Values = context.PersonTyp.Select(pt => new DataContracts.Analys.LookupValue { ID = pt.Id, Value = pt.Benamning }).OrderBy(v => v.Value).ToList()
			});

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "StadsdelID",
				Values = context.Stadsdel.Select(s => new DataContracts.Analys.LookupValue { ID = s.Id, Value = s.Benamning }).OrderBy(v => v.Value).ToList()
			});

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "ByggnadStatusID",
				Values = new List<DataContracts.Analys.LookupValue>
				{ 
					new DataContracts.Analys.LookupValue { ID = DataContracts.ByggnadStatus.InaktivID, Value = "Inaktiv" },
					new DataContracts.Analys.LookupValue { ID = DataContracts.ByggnadStatus.AktivID, Value = "Aktiv" }
				}
			});

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "PersonStatusID",
				Values = new List<DataContracts.Analys.LookupValue>
				{
					new DataContracts.Analys.LookupValue { ID = DataContracts.PersonStatus.InaktivID, Value = "Inaktiv" },
					new DataContracts.Analys.LookupValue { ID = DataContracts.PersonStatus.AktivID, Value = "Aktiv" }
				}
			});

			data.Add(new DataContracts.Analys.LookupField
			{
				FieldName = "PersonKonID",
				Values = new List<DataContracts.Analys.LookupValue>
				{
					new DataContracts.Analys.LookupValue { ID = DataContracts.PersonKon.KvinnaID, Value = "Kvinna" },
					new DataContracts.Analys.LookupValue { ID = DataContracts.PersonKon.ManID, Value = "Man" }
				}
			});

			return data;
		}

		internal List<DataContracts.Analys.Entity> GetEntities()
		{
			List<DataContracts.Analys.Entity> entities = new List<DataContracts.Analys.Entity>();

			DataContracts.Analys.Entity byggnad = new DataContracts.Analys.Entity();
			byggnad.Name = "Byggnad";
			byggnad.CannotBeCombinedWith = new List<string> { "Person", "Kurstillfalle", "Grupp", "Lärare", "Uppdrag", "MaterielTyp" };
			byggnad.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "ByggnadBenamning", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Namn", NameInGrid = "Byggnad namn" },
				new DataContracts.Analys.Field { InternalName = "ByggnadTyp", LookupColumn="ByggnadTypID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Typ", NameInGrid = "Byggnadtyp" },
				new DataContracts.Analys.Field { InternalName = "ByggnadBesokAdress", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Besöksadress", NameInGrid = "Byggnad besöksadress" },
				new DataContracts.Analys.Field { InternalName = "ByggnadBesokPostNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Besökspostnummer", NameInGrid = "Byggnad besökspostnr" },
				new DataContracts.Analys.Field { InternalName = "ByggnadBesokPostOrt", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Besöksort", NameInGrid = "Byggnad besöksort" },
				new DataContracts.Analys.Field { InternalName = "ByggnadLeveransAdress", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Leveransadress", NameInGrid = "Byggnad leveransadress" },
				new DataContracts.Analys.Field { InternalName = "ByggnadLeveransPostNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Leveranspostnummer", NameInGrid = "Byggnad leveranspostnr" },
				new DataContracts.Analys.Field { InternalName = "ByggnadLeveransPostOrt", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Leveransort", NameInGrid = "Byggnad leveransort" },
				new DataContracts.Analys.Field { InternalName = "ByggnadStadsdel", LookupColumn="StadsdelID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Stadsdel", NameInGrid = "Byggnad stadsdel" },
				new DataContracts.Analys.Field { InternalName = "ByggnadStatus", LookupColumn="ByggnadStatusID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Status", NameInGrid = "Byggnad status" },
				new DataContracts.Analys.Field { InternalName = "ByggnadHemsida", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Hemsida", NameInGrid = "Byggnad hemsida" },
				new DataContracts.Analys.Field { InternalName = "ByggnadTelNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Telefonnummer", NameInGrid = "Byggnad telefonnummer" },
				new DataContracts.Analys.Field { InternalName = "ByggnadEpost", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Epost", NameInGrid = "Byggnad epost" }
			};
			entities.Add(byggnad);

			DataContracts.Analys.Entity lokal = new DataContracts.Analys.Entity();
			lokal.Name = "Lokal";
			lokal.CannotBeCombinedWith = new List<string> { "Person", "Kurstillfalle", "Grupp", "Materiel", "Kontaktperson", "Lärare", "Uppdrag", "MaterielTyp" };
			lokal.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "LokalBenamning", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Namn", NameInGrid = "Lokal namn" }
			};
			entities.Add(lokal);

			DataContracts.Analys.Entity person = new DataContracts.Analys.Entity();
			person.Name = "Person";
			person.CannotBeCombinedWith = new List<string> { "Byggnad", "Lokal", "Materiel", "Kontaktperson", "Lärare", "Valkrets", "Forsamling", "Valdistrikt", "Grupp", "MaterielTyp" };
			person.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "PersonPersonnummer", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Personnummer", NameInGrid = "Person personnummer" },
				new DataContracts.Analys.Field { InternalName = "PersonFornamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Förnamn", NameInGrid = "Person förnamn" },
				new DataContracts.Analys.Field { InternalName = "PersonEfternamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Efternamn", NameInGrid = "Person efternamn" },
				new DataContracts.Analys.Field { InternalName = "PersonEpost", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Epost", NameInGrid = "Person E-post" },
				new DataContracts.Analys.Field { InternalName = "PersonTelNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Telefon", NameInGrid = "Person telefon" },
				new DataContracts.Analys.Field { InternalName = "PersonMobTelNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Mobiltelefon", NameInGrid = "Person mobiltelefon" },
				new DataContracts.Analys.Field { InternalName = "PersonAdress", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Adress", NameInGrid = "Person adress" },
				new DataContracts.Analys.Field { InternalName = "PersonPostNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Postnummer", NameInGrid = "Person postnr" },
				new DataContracts.Analys.Field { InternalName = "PersonPostOrt", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Postort", NameInGrid = "Person postort" },
				new DataContracts.Analys.Field { InternalName = "PersonStatus", LookupColumn="PersonStatusID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Status", NameInGrid = "Person status" },
				new DataContracts.Analys.Field { InternalName = "PersonTyp", LookupColumn="PersonTypID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Typ", NameInGrid = "Persontyp" },
				new DataContracts.Analys.Field { InternalName = "PersonKon", LookupColumn = "PersonKonID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Kön", NameInGrid = "Kön" },
				new DataContracts.Analys.Field { InternalName = "PersonAlder", DataType = typeof(int).FullName, DataTypeInGrid = typeof(int).FullName, NameInQueryBuilder = "Ålder", NameInGrid = "Ålder" }
			};
			entities.Add(person);

			DataContracts.Analys.Entity kurstillfalle = new DataContracts.Analys.Entity();
			kurstillfalle.Name = "Kurstillfalle";
			kurstillfalle.CannotBeCombinedWith = new List<string> { "Byggnad", "Lokal", "Valkrets", "Forsamling", "Valdistrikt", "Grupp", "Materiel", "Kontaktperson", "Uppdrag", "MaterielTyp" };
			kurstillfalle.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "KursTillfalleDatum", DataType = typeof(string).FullName, DataTypeInGrid = typeof(DateTime).FullName, NameInQueryBuilder = "Datum", NameInGrid = "Kurstillfälle datum" },
				new DataContracts.Analys.Field { InternalName = "KursTillfalleStarttid", DataType = typeof(string).FullName, DataTypeInGrid = typeof(TimeSpan).FullName, NameInQueryBuilder = "Starttid", NameInGrid = "Kurstillfälle starttid" },
				new DataContracts.Analys.Field { InternalName = "KursTillfalleSluttid", DataType = typeof(string).FullName, DataTypeInGrid = typeof(TimeSpan).FullName, NameInQueryBuilder = "Sluttid", NameInGrid = "Kurstillfälle sluttid" },
				new DataContracts.Analys.Field { InternalName = "KursTillfalleAdress", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Adress", NameInGrid = "Kurstillfälle adress" },
				new DataContracts.Analys.Field { InternalName = "KursTillfalleSal", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Sal", NameInGrid = "Kurstillfälle sal" },
				new DataContracts.Analys.Field { InternalName = "KursTillfalleKursBeskrivning", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Kursbeskrivning", NameInGrid = "Kurs beskrivning" },
				new DataContracts.Analys.Field { InternalName = "KursTillfalleKursInfo", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Kursinformation", NameInGrid = "Kurs information" },
				new DataContracts.Analys.Field { InternalName = "KursTillfalleLokal", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Lokal", NameInGrid = "Kurs lokal" }
			};
			entities.Add(kurstillfalle);

			DataContracts.Analys.Entity valkrets = new DataContracts.Analys.Entity();
			valkrets.Name = "Valkrets";
			valkrets.CannotBeCombinedWith = new List<string> { "Person", "Kurstillfalle", "Grupp", "Materiel", "Kontaktperson", "Lärare", "Uppdrag", "MaterielTyp" };
			valkrets.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "ValkretsNamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Namn", NameInGrid = "Valkrets namn" },
				new DataContracts.Analys.Field { InternalName = "ValkretsNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Nummer", NameInGrid = "Valkrets nummer" }
			};
			entities.Add(valkrets);

			DataContracts.Analys.Entity forsamling = new DataContracts.Analys.Entity();
			forsamling.Name = "Forsamling";
			forsamling.CannotBeCombinedWith = new List<string> { "Person", "Kurstillfalle", "Grupp", "Materiel", "Kontaktperson", "Lärare", "Uppdrag", "MaterielTyp" };
			forsamling.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "ForsamlingNamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Namn", NameInGrid = "Församling namn" },
				new DataContracts.Analys.Field { InternalName = "ForsamlingNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Nummer", NameInGrid = "Församling nummer" }
			};
			entities.Add(forsamling);

			DataContracts.Analys.Entity valdistrikt = new DataContracts.Analys.Entity();
			valdistrikt.Name = "Valdistrikt";
			valdistrikt.CannotBeCombinedWith = new List<string> { "Kurstillfalle", "Grupp", "Materiel", "Kontaktperson", "Lärare", "Uppdrag", "MaterielTyp" };
			valdistrikt.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "ValdistriktNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Nummer", NameInGrid = "Valdistrikt nummer" }
			};
			entities.Add(valdistrikt);

			DataContracts.Analys.Entity grupp = new DataContracts.Analys.Entity();
			grupp.Name = "Grupp";
			grupp.CannotBeCombinedWith = new List<string> { "Kurstillfalle", "Valdistrikt", "Forsamling", "Valkrets", "Byggnad", "Lokal", "Materiel", "Kontaktperson", "Lärare", "Uppdrag", "MaterielTyp" };
			grupp.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "GruppNamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Namn", NameInGrid = "Grupp namn" }
			};
			entities.Add(grupp);

			DataContracts.Analys.Entity materiel = new DataContracts.Analys.Entity();
			materiel.Name = "Materiel";
			materiel.CannotBeCombinedWith = new List<string> { };
			materiel.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "MaterielBeskrivning", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Beskrivning", NameInGrid = "Materiel beskrivning" },
				new DataContracts.Analys.Field { InternalName = "MaterielAntal", DataType = typeof(int).FullName, DataTypeInGrid = typeof(int).FullName, NameInQueryBuilder = "Antal", NameInGrid = "Materiel antal" }
			};
			entities.Add(materiel);

			DataContracts.Analys.Entity kontaktperson = new DataContracts.Analys.Entity();
			kontaktperson.Name = "Kontaktperson";
			kontaktperson.CannotBeCombinedWith = new List<string> { };
			kontaktperson.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "PersonPersonnummer", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Personnummer", NameInGrid = "Kontaktperson personnummer" },
				new DataContracts.Analys.Field { InternalName = "PersonFornamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Förnamn", NameInGrid = "Kontaktperson förnamn" },
				new DataContracts.Analys.Field { InternalName = "PersonEfternamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Efternamn", NameInGrid = "Kontaktperson efternamn" },
				new DataContracts.Analys.Field { InternalName = "PersonEpost", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Epost", NameInGrid = "Kontaktperson E-post" },
				new DataContracts.Analys.Field { InternalName = "PersonTelNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Telefon", NameInGrid = "Kontaktperson telefon" },
				new DataContracts.Analys.Field { InternalName = "PersonMobTelNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Mobiltelefon", NameInGrid = "Kontaktperson mobiltelefon" },
				new DataContracts.Analys.Field { InternalName = "PersonAdress", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Adress", NameInGrid = "Kontaktperson adress" },
				new DataContracts.Analys.Field { InternalName = "PersonPostNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Postnummer", NameInGrid = "Kontaktperson postnr" },
				new DataContracts.Analys.Field { InternalName = "PersonPostOrt", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Postort", NameInGrid = "Kontaktperson postort" },
				new DataContracts.Analys.Field { InternalName = "PersonStatus", LookupColumn="PersonStatusID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Status", NameInGrid = "Kontaktperson status" },
				new DataContracts.Analys.Field { InternalName = "PersonTyp", LookupColumn="PersonTypID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Typ", NameInGrid = "Kontaktperson typ" },
				new DataContracts.Analys.Field { InternalName = "PersonKon", LookupColumn = "PersonKonID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Kön", NameInGrid = "Kontaktperson kön" },
				new DataContracts.Analys.Field { InternalName = "PersonAlder", DataType = typeof(int).FullName, DataTypeInGrid = typeof(int).FullName, NameInQueryBuilder = "Ålder", NameInGrid = "Kontaktperson ålder" }
			};
			entities.Add(kontaktperson);

			DataContracts.Analys.Entity larare = new DataContracts.Analys.Entity();
			larare.Name = "Lärare";
			larare.CannotBeCombinedWith = new List<string> { };
			larare.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "LararePersonnummer", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Personnummer", NameInGrid = "Lärare personnummer" },
				new DataContracts.Analys.Field { InternalName = "LarareFornamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Förnamn", NameInGrid = "Lärare förnamn" },
				new DataContracts.Analys.Field { InternalName = "LarareEfternamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Efternamn", NameInGrid = "Lärare efternamn" },
				new DataContracts.Analys.Field { InternalName = "LarareEpost", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Epost", NameInGrid = "Lärare E-post" },
				new DataContracts.Analys.Field { InternalName = "LarareTelNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Telefon", NameInGrid = "Lärare telefon" },
				new DataContracts.Analys.Field { InternalName = "LarareMobTelNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Mobiltelefon", NameInGrid = "Lärare mobiltelefon" },
				new DataContracts.Analys.Field { InternalName = "LarareAdress", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Adress", NameInGrid = "Lärare adress" },
				new DataContracts.Analys.Field { InternalName = "LararePostNr", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Postnummer", NameInGrid = "Lärare postnr" },
				new DataContracts.Analys.Field { InternalName = "LararePostOrt", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Postort", NameInGrid = "Lärare postort" },
				new DataContracts.Analys.Field { InternalName = "LarareStatus", LookupColumn="PersonStatusID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Status", NameInGrid = "Lärare status" },
				new DataContracts.Analys.Field { InternalName = "LarareTyp", LookupColumn="PersonTypID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Typ", NameInGrid = "Lärare typ" },
				new DataContracts.Analys.Field { InternalName = "LarareKon", LookupColumn = "PersonKonID", DataType = typeof(List<string>).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Kön", NameInGrid = "Lärare kön" },
				new DataContracts.Analys.Field { InternalName = "LarareAlder", DataType = typeof(int).FullName, DataTypeInGrid = typeof(int).FullName, NameInQueryBuilder = "Ålder", NameInGrid = "Lärare ålder" }
			};
			entities.Add(larare);

			DataContracts.Analys.Entity uppdrag = new DataContracts.Analys.Entity();
			uppdrag.Name = "Uppdrag";
			uppdrag.CannotBeCombinedWith = new List<string> { };
			uppdrag.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "GruppNamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Grupp", NameInGrid = "Uppdrag grupp" },
                new DataContracts.Analys.Field { InternalName = "ValkretsNamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Valkrets", NameInGrid = "Uppdrag valkrets" },
                new DataContracts.Analys.Field { InternalName = "ForsamlingNamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Församling", NameInGrid = "Uppdrag församling" },
                new DataContracts.Analys.Field { InternalName = "ValdistriktNamn", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Valdistrikt", NameInGrid = "Uppdrag valdistrikt" },
				new DataContracts.Analys.Field { InternalName = "UppdragFunktion", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Funktion", NameInGrid = "Uppdrag funktion" },
				new DataContracts.Analys.Field { InternalName = "UppdragPublikFunktion", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Publik funktion", NameInGrid = "Uppdrag publik funktion" }
			};
			entities.Add(uppdrag);

			DataContracts.Analys.Entity materieltyp = new DataContracts.Analys.Entity();
			materieltyp.Name = "MaterielTyp";
			materieltyp.CannotBeCombinedWith = new List<string> { "Person", "Kurstillfalle", "Grupp", "Valdistrikt", "Forsamling", "Valkrets", "Lokal", "Kontaktperson", "Lärare", "Uppdrag", "Byggnad", "Materiel" };
			materieltyp.Fields = new List<DataContracts.Analys.Field>
			{
				new DataContracts.Analys.Field { InternalName = "MaterielTypParentBeskrivning", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Beskrivning", NameInGrid = "Materiel beskrivning" },
				new DataContracts.Analys.Field { InternalName = "MaterielTypChildBeskrivning", DataType = typeof(string).FullName, DataTypeInGrid = typeof(string).FullName, NameInQueryBuilder = "Underliggande typer", NameInGrid = "Materiel underliggande typ" }
			};
			entities.Add(materieltyp);

			return entities;
		}

		internal void SaveQuery(DataContracts.Analys.Query query, string loginName)
		{
			//AnalyseQueryMapper mapper = new AnalyseQueryMapper(this.context);
			bool doAdd = true;

			AnalysQuery efObject = null;
			if (query.QueryID != 0)
				efObject = context.AnalysQueries.FirstOrDefault(aq => aq.AnalysQueryId == query.QueryID);

			if (efObject == null)
				efObject = new AnalysQuery();
			else
				doAdd = false;

			efObject = query.ToEntity(efObject);
			if (!String.IsNullOrEmpty(loginName))
				efObject.LoginName = loginName;

			if (doAdd)
			{
				this.context.AnalysQueries.Add(efObject);
			}
			this.context.SaveChanges();
		}

		internal List<DataContracts.Analys.Query> GetQueriesByPerson(string loginName)
		{
			return (from a in context.AnalysQueries
					where string.IsNullOrEmpty(a.LoginName) || a.LoginName == loginName
					orderby a.Namn ascending
					select a).ToList().Select(q => q.ToContract()).ToList();
		}

		internal DataContracts.Analys.Query GetQueryById(int id)
		{
			DataContracts.Analys.Query q = context.AnalysQueries.First(aq => aq.AnalysQueryId == id).ToContract();
			return q;
		}

		internal void DeleteQuery(int id)
		{
			var q = context.AnalysQueries.First(aq => aq.AnalysQueryId == id);
			context.AnalysQueries.Remove(q);
			context.SaveChanges();
		}
	}
}
